package com.tuanchau.android.englishtooltip.database;

import java.util.LinkedList;
import java.util.Vector;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DatabaseManager extends SQLiteOpenHelper{

	public static final String DB_NAME = "dictionaryDatabase";
	public static final String TABLE_NAME = "tblDict";
	public static final String COL_ID = "id";
	public static final String COL_WORD = "word";
	public static final String COL_TYPE = "type";
	public static final String COL_PRON = "pronunciation";
	public static final String COL_MEAN = "mean";
	public static final String COL_IMAGE = "image";
	
	public DatabaseManager(final Context context){
		super(context, DB_NAME, null, 1);
	}
	
	@Override
	public void onCreate(final SQLiteDatabase db) {
		db.execSQL("CREATE TABLE " + TABLE_NAME + " ( " +
			COL_ID + " INTEGER PRIMARY KEY, " +
			COL_WORD + " TEXT, " +
			COL_TYPE + " TEXT, " +
			COL_PRON + " TEXT, " +
			COL_MEAN + " TEXT, " +
			COL_IMAGE + " BLOB " + ");"
		);
		
		
	}

	@Override
	public void onUpgrade(final SQLiteDatabase db, final int oldVersion, final int newVersion) {
		db.execSQL("DROP TABLE IF EXIST " + TABLE_NAME);
		onCreate(db);
	}
	
	public void insert(final int id, final String word, final String type, final String pron, final String mean, final  byte[] image) {
		final SQLiteDatabase db = this.getWritableDatabase();
		final ContentValues cv = new ContentValues();
		
		cv.put(COL_ID, id);
		cv.put(COL_WORD, word);
		cv.put(COL_TYPE, type);
		cv.put(COL_PRON, pron);
		cv.put(COL_MEAN, mean);
		cv.put(COL_IMAGE, image);
		
		db.insert(TABLE_NAME, COL_ID, cv);
		db.close();
	}
	
	public void insert(WordStruct w){
		insert(w.id, w.word, w.type, w.pron, w.mean, w.image);
	}
	
	public void update(int id, String word, String type, String pron, String mean, byte[] image) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues cv = new ContentValues();
		
		//cv.put(colId, id);
		cv.put(COL_WORD, word);
		cv.put(COL_TYPE, type);
		cv.put(COL_PRON, pron);
		cv.put(COL_MEAN, mean);
		cv.put(COL_IMAGE, image);
		
		db.update(TABLE_NAME, cv, COL_ID + "=?", new String[]{String.valueOf(id)});
		db.close();
	}
	
	public void update(WordStruct w){
		update(w.id, w.word, w.type, w.pron, w.mean, w.image);
	}
	
	public void delete(int id){
		SQLiteDatabase db = this.getWritableDatabase();
		db.delete(TABLE_NAME, COL_ID + "=?", new String[]{String.valueOf(id)});
		db.close();
	}
	
	public LinkedList<WordStruct> getAll() {
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cur = db.rawQuery("SELECT * from " + TABLE_NAME, new String[]{});
		int count = cur.getCount();
		
		LinkedList<WordStruct>result = new LinkedList<WordStruct>();
		
		int iId = cur.getColumnIndex(COL_ID);
		int iWord = cur.getColumnIndex(COL_WORD);
		int iType = cur.getColumnIndex(COL_TYPE);
		int iPron = cur.getColumnIndex(COL_PRON);
		int iMean = cur.getColumnIndex(COL_MEAN);
		int iImage = cur.getColumnIndex(COL_IMAGE);
		cur.moveToFirst();
		for(int i = 0; i < count; i++){
			WordStruct w = new WordStruct();
			w.id = cur.getInt(iId);
			w.word = cur.getString(iWord);
			w.type = cur.getString(iType);
			w.pron = cur.getString(iPron);
			w.mean = cur.getString(iMean);
			w.image = cur.getBlob(iImage);
			if(w.image != null && w.image.length < 10)
				w.image = null;
			result.add(w);
			if(!cur.moveToNext())
				break;
		}
		cur.close();
		db.close();
		return result;
	}
	
	public Vector<Integer> getIds(){
		Vector<Integer> result = new Vector<Integer>(10);
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cur = db.rawQuery("SELECT "+COL_ID + " from " + TABLE_NAME, new String[]{});
		int count = cur.getCount();
		int iId = cur.getColumnIndex(COL_ID);
		cur.moveToFirst();
		for(int i = 0; i < count; i++) {
			result.add(cur.getInt(iId));
			cur.moveToNext();
		}
		cur.close();
		db.close();
		return result;
	}
	
	public WordStruct getAtId(int id) {
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cur = db.rawQuery("SELECT * from " + TABLE_NAME + " WHERE " + COL_ID + " = " + id, new String[]{});
		WordStruct w = null;
		if(cur.moveToFirst()){
			cur.moveToFirst();
			int iId = cur.getColumnIndex(COL_ID);
			int iWord = cur.getColumnIndex(COL_WORD);
			int iType = cur.getColumnIndex(COL_TYPE);
			int iPron = cur.getColumnIndex(COL_PRON);
			int iMean = cur.getColumnIndex(COL_MEAN);
			int iImage = cur.getColumnIndex(COL_IMAGE);
			
			w = new WordStruct();
			w.id = cur.getInt(iId);
			w.word = cur.getString(iWord);
			w.type = cur.getString(iType);
			w.pron = cur.getString(iPron);
			w.mean = cur.getString(iMean);
			w.image = cur.getBlob(iImage);
			if(w.image != null && w.image.length < 10)
				w.image = null;
			
		}
		cur.close();
		db.close();
		return w;
	}
	
	
}
